<cfcomponent name="query2xls" displayname="query2xls">

<!---
works in an inline page, not from cfc ??
		<cfscript>
	  context = getPageContext();
	  context.setFlushOutput(false);
	  response = context.getResponse().getResponse();
	  outStream = response.getOutputStream();
	  response.setContentType("application/msexcel");
		workbook = createObject("java","jxl.Workbook");
	  ws = createObject("java","jxl.WorkbookSettings").init();
		locale=createObject("java","java.util.Locale").init("en","EN");
	  ws.setLocale(locale);
		workbook = workbook.createWorkbook(outStream,ws);
		variables.labelObj=createObject("java","jxl.write.Label");
		variables.numberObj=createObject("java","jxl.write.Number");
		firstSheet = workBook.createSheet("First Sheet", 0);
		thisLabel=variables.labelObj.init(0,0,"happy happy gizmo");
		firstSheet.addCell(thisLabel);
		thisNumber=variables.numberObj.init(0, 1, 3.1459);
		firstSheet.addCell(thisNumber);
		workbook.write();
		outStream.flush();
		workbook.close();
		</cfscript>

			var outStream = createObject("java","java.io.ByteArrayOutputStream").init();
			var workbook = createObject("java","jxl.Workbook");
			//var outFile = createObject("java","java.io.File").init("\tmp\xls.xls");
			//var workBook=createObject("java","jxl.Workbook").createWorkbook(outFile);
			var locale=createObject("java","java.util.Locale").init("en","EN");
	    var ws = createObject("java","jxl.WorkbookSettings").init();
			workbook = workbook.createWorkbook(outStream,ws);


 --->

	<cffunction name="init" access="public" output="false" hint="I initialize the query2xls object">
		<cfset variables.excelSheets = structNew()>
	</cffunction>

	<cffunction name="addSheetQuery" access="public" output="false" hint="I initialize the query2xls object">
		<cfargument name="sheetName" required="true" type="string">
		<cfargument name="query" required="true" type="query">
		<cfset variables.excelSheets[structCount(variables.excelSheets)+1][arguments.sheetName] = arguments.query>
	</cffunction>

	<cffunction name="getSheetQueries" access="public" output="false" hint="I initialize the query2xls object">
		<cfreturn variables.excelSheets>
	</cffunction>

	<cffunction name="dumpVar" output="true">
		<cfargument name="daVar">
		<cfdump var="#daVar#">
		<cfabort>
	</cffunction>

	<cffunction name="createJavaWorkbook" output="false" returntype="any">
		<cfargument name="outStream">
		<cfscript>
		var loader = "";
		var workbook  = structNew();
		var paths = arrayNew(1);
	  var ws = "";
		var locale="";
			/*
			This points to the jar we want to load.
			Could also load a directory of .class files
			*/
		paths[1] = getDirectoryFromPath(getCurrentTemplatePath()) & "jxl.jar";
			//create the loader
		loader = createObject("component", "javaloader.JavaLoader").init(paths);
			//at this stage we only have access to the class, but we don't have an instance
		workbook = loader.create("jxl.Workbook");
		//dumpvar(workbook.getVersion());
	  ws = loader.create("jxl.WorkbookSettings");
		locale=loader.create("java.util.Locale").init("en","EN");
		variables.labelObj=loader.create("jxl.write.Label");
		variables.numberObj=loader.create("jxl.write.Number");
		variables.formulaObj = loader.create("jxl.write.Formula");
		ws.setLocale(locale);
		workbook = workbook.createWorkbook(arguments.outStream,ws);

		 return workbook ;
		</cfscript>
	</cffunction>

	<cffunction name="toXLS" output="false" hint="I create a XLS workbook from variables.excelSheets">
		<cfreturn queriesToXLS(variables.excelSheets)>
	</cffunction>

	<cffunction name="queriesToXLS" output="false" hint="I create a XLS workbook from a struct of queries">
		<cfargument name="inQueries" type="struct" required="true">
		<cfargument name="inFormulas" type="struct" default="#structNew()#">
		<cfscript>
		var outStream = createObject("java","java.io.ByteArrayOutputStream").init();
		var workbook = createJavaWorkbook(outStream);
		var sheetFormulas = structNew();
		var formulas = structNew();
		for (i = 1; i lte structCount(inQueries); i = i + 1) {
			sheetName = structKeyList(inQueries[i]);
			sheetQuery = inQueries[i][structKeyList(inQueries[i])];
			// we use getMetaData() to preserve column order
			queryCols = arrayToList(sheetQuery.getMetaData().getColumnLabels());
			if (structCount(inFormulas) gte i) {
				for (fCol = 1; fCol lte listLen(inFormulas[i]["formulas"]); fCol = fCol + 1) {
					sheetFormula = listGetAt(inFormulas[i]["formulas"],fCol);
					formulaField = lcase(listFirst(sheetFormula," ="));
					formula = lcase(listLast(sheetFormula,"="));
					for (qCol = 1; qCol lte listLen(queryCols); qCol = qCol + 1) {
						formula = replaceNoCase(formula,listGetAt(queryCols,qCol),chr(64+qCol)& "CURROW") ;
					}
					formulas[formulaField] = ucase(formula);
				}
			}
			//dumpvar(formulas);
			sheet = workBook.createSheet(sheetname, toString(i-1));
			// ouput column headers
			for (qCol = 1; qCol lte listLen(queryCols); qCol = qCol + 1) {
				thisLabel = variables.labelObj.init(toString(val(qCol)-1),0,listGetAt(queryCols,qCol));
				sheet.addCell(thisLabel);
			}
			for (qRow = 1; qRow lte sheetQuery.recordcount; qRow = qRow + 1) {
				for (qCol = 1; qCol lte listLen(queryCols); qCol = qCol + 1) {
					curColl = lcase(listGetAt(queryCols,qCol));
					if( listFind(structKeyList(formulas),curColl)) {
						// see if this is a formula field
					  thisLabel = variables.formulaObj.init(javaCast("int",qCol-1),javaCast("int",qRow),replace(formulas[curColl],"CURROW",qRow+1,"all"));
					  //dumpVar(replace(formula,"CURROW",qRow+1,"all"));
					}
					else {
						thisLabel = variables.labelObj.init(toString(val(qCol)-1),toString(qRow),toString(sheetQuery[listGetAt(queryCols,qCol)][qRow]));
					}
					sheet.addCell(thisLabel);
				}
			}
		}
		workbook.write();
		outStream.flush();
		workbook.close();
		outStream.close();
		return outStream.toByteArray();
		</cfscript>
		</cffunction>	
	</cfcomponent>